设为首页收藏本站
开启辅助访问

How to Filter by Multiple Colors in Excel (2 Easy Methods)

 您所在的位置：网站首页 › excel get color › How to Filter by Multiple Colors in Excel (2 Easy Methods)

How to Filter by Multiple Colors in Excel (2 Easy Methods)

#How to Filter by Multiple Colors in Excel (2 Easy Methods)| 来源: 网络整理| 查看: 265

In this article, I will discuss how you can filter by multiple colors in Microsoft Excel. Usually, when we filter a certain column by multiple colors, other columns present in the dataset are too filtered according to the first filter applied. Filtering data by multiple colors is a bit tricky. However, I will show you two suitable ways how to filter by multiple colors in Excel effective with appropriate illustrations.

Table of Contents hide Download Practice Workbook 2 Suitable Ways to Filter by Multiple Colors in Excel 1. Use Color Code to Filter by Multiple Colors in Excel 2. Run VBA Code to Filter by Multiple Colors in Excel Things to Remember Conclusion Related Articles Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Filter by Multiple Colors.xlsm 2 Suitable Ways to Filter by Multiple Colors in Excel

Suppose we have the below dataset containing several sales representatives’ names and their region of sales, and revenue earned by the sales representative are given in columns B, C, and E respectively. We will create a User Defined Function using VBA code and manually using the Color code to filter data by multiple colors. Here’s the overview of our dataset.

Filter by Multiple Colors in Excel

1. Use Color Code to Filter by Multiple Colors in Excel

In this method, I will filter our data by multiple colors only using the color code option of Excel. To do that, you have to know the cell’s color code. The code for Red is 3, for Yellow 6, for Green 14, and so on. Let’s follow the instructions below to filter by multiple colors!

Step 1:

First, select any cell in the dataset, then from Excel Ribbon, select the Filter command from the Data tab to apply the Filter.

Use Color Code to Filter by Multiple Colors in Excel

As a result, the drop-down arrow for the filter appears.

Next click on the drop-down arrow for column D, and check the 3, 6, and 14 options to Filter by Color code. At last, press the OK option.

Use Color Code to Filter by Multiple Colors in Excel

As a result, you will be able to filter by multiple colors using the color code which has been given in the below screenshot.

Step 2:

You also can do that using the Sort To do that, click on the drop-down arrow for column C, select Sort by Color, and then choose Custom Sort.

Use Color Code to Filter by Multiple Colors in Excel

Hence, a dialog box named Sort will appear in front of you. From the Sort dialog box, firstly, select Region under the Sort by drop-down list. After that, select Cell Color under the Sort On drop-down list. Hence, select the Red color under the Order drop-down list. Further, press the Add Level to add several colors.

Use Color Code to Filter by Multiple Colors in Excel

After that, do like in the below screenshot using Add Level At last, press the OK option.

Use Color Code to Filter by Multiple Colors in Excel

As a result, you will be able to filter by multiple colors using the Custom Sort command which has been given in the below screenshot.

Use Color Code to Filter by Multiple Colors in Excel

Read More: How to Filter by Color in Excel (2 Examples)

2. Run VBA Code to Filter by Multiple Colors in Excel

This time, I will use a simple VBA code to create a User Defined function and thus find the color index of each color I have applied to cells of column D. Then I will filter column D for multiple colors. Let’s follow the instructions below to filter by multiple colors using a simple VBA code!

Step 1:

First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

Run VBA Code to Filter by Multiple Colors in Excel

After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Filter by Multiple Colors will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Run VBA Code to Filter by Multiple Colors in Excel

Hence, the Filter by Multiple Colors module pops up. In the Filter by Multiple Colors module, write down the below VBA Function FindColor(n As Range) As Integer FindColor = n.Interior.ColorIndex End Function

Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Run VBA Code to Filter by Multiple Colors in Excel

Step 2:

Then come to the worksheet where you want to apply the newly created function, type the below formula in Cell E5, and hit Enter on your keyboard. The User Defined Function is, =FindColor(D5)

After entering the above formula we will get the color index of Cell D5. Later, I used the AutoFill Handle (+) tool to get the color index for the rest of the colored cells of column D.

Run VBA Code to Filter by Multiple Colors in Excel

Now, we will apply the Filter option to the dataset using Ctrl + Shift + L. Initially, click on the filter drop-down arrow of column E and put a checkmark only for the color index 3, 6, 14, and At last, press OK.

As a result, you will be able to filter by multiple colors using the VBA code which has been given in the below screenshot.

Run VBA Code to Filter by Multiple Colors in Excel

Read More: How to Filter Multiple Columns by Color in Excel (2 Methods)

Things to Remember

➜ You can use Ctrl + Shift + L as the keyboard shortcut for filtering data.

➜ If your Developer tab is not visible, you will press Alt + F11 to enable macros.

Conclusion

I hope all of the suitable methods mentioned above to filter by multiple colors will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries. And please visit our website ExcelDemy to explore more.

Related Articles How to Filter by Color Using Conditional Formatting in Excel Excel Filter by Color and Text (with Easy Steps) How to Remove Filter by Color in Excel (5 Methods) SaveSavedRemoved 0

Tags: Color Filter in Excel

Md. Abdur Rahim Rasel Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

Related Articles Added to wishlistRemoved from wishlist 0 excel metadata extractor How to Make an Excel Metadata Extractor (with Easy Steps) Added to wishlistRemoved from wishlist 0 linear programming sensitivity analysis excel How to Do Linear Programming with Sensitivity Analysis in Excel Added to wishlistRemoved from wishlist 0 Excel data validation checkbox How to Create Data Validation with Checkbox Control in Excel Added to wishlistRemoved from wishlist 1 excel queries and connections not working [Solved!] Excel Queries and Connections Not Working We will be happy to hear your thoughts Leave a reply Cancel reply


【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3